﻿using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Collections;
using System.Reflection;
using System.Configuration;
using System.Collections.Generic;
using System.Data.SqlClient;


namespace Utils
{
    public class DBHelper
    {
        private static readonly string dbProviderName = ConfigurationManager.AppSettings["DbHelperProvider"];
        private static readonly string dbConnectionString = ConfigurationManager.AppSettings["DbHelperConnectionString"];

        private readonly DbConnection connection;

        #region 构造与初始化链接

        public DBHelper()
        {
            this.connection = CreateConnection(DBHelper.dbConnectionString);
        }

        public DBHelper(string connectionString)
        {
            this.connection = CreateConnection(connectionString);
        }



        public static DbConnection CreateConnection()
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);
            DbConnection dbconn = dbfactory.CreateConnection();
            dbconn.ConnectionString = DBHelper.dbConnectionString;
            return dbconn;
        }

        public static DbConnection CreateConnection(string connectionString)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);
            DbConnection dbconn = dbfactory.CreateConnection();
            dbconn.ConnectionString = connectionString;
            return dbconn;
        }

        public DbCommand GetStoredProcCommond(string storedProcedure)
        {
            DbCommand dbCommand = connection.CreateCommand();
            SetCommandParaClear(dbCommand);
            dbCommand.CommandText = storedProcedure;
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.Connection = CreateConnection();
            return dbCommand;
        }

        public DbCommand GetSqlStringCommond(string sqlQuery)
        {
            DbCommand dbCommand = connection.CreateCommand();
            SetCommandParaClear(dbCommand);
            dbCommand.CommandText = sqlQuery;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.Connection = CreateConnection();
            return dbCommand;
        }

        public void SetConnClose(DbConnection connection)
        {
            if (connection.State != System.Data.ConnectionState.Closed)
            {
                connection.Close();
                connection.Dispose();
            }
        }

        #endregion

        #region 参数处理

        #region in输入参数

        public void AddParameterCollection(ref DbCommand cmd, params DbParameter[] paras)
        {
            SetCommandParaClear(cmd);
            foreach (DbParameter dbParameter in paras)
            {
                cmd.Parameters.Add(dbParameter);
            }
        }


        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="value"></param>
        public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, int size, object value)
        {
            AddInParameter(cmd, parameterName, value);
        }



        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        public void AddInParameter(DbCommand cmd, string parameterName, object value)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            cmd.Parameters.Add(dbParameter);
        }


        /// <summary>
        /// 全新加载参数(先清除，后处理参数)
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="dict"></param>
        public void AddInParameter(ref DbCommand cmd, Dictionary<string, object> dict)
        {
            SetCommandParaClear(cmd); //

            if (dict != null)
            {
                foreach (string key in dict.Keys)
                {
                    object obj = new object();
                    dict.TryGetValue(key, out obj);
                    if (obj != null)
                    {
                        string para = key;
                        if (!key.StartsWith("@"))
                        {
                            para = "@" + key;
                        }
                        AddInParameter(cmd, para, obj);
                    }
                }
            }
        }

        #endregion

        #region out 输出参数

        /// <summary>
        /// 适用返回参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Size = size;
            dbParameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(dbParameter);
        }

        public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size, object obj)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Size = size;
            dbParameter.Direction = ParameterDirection.Output;
            dbParameter.Value = obj;
            cmd.Parameters.Add(dbParameter);
        }

        #endregion

        #region Return返回参数

        /// <summary>
        /// 适用返回参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(dbParameter);
        }

        #endregion

        #region 读取输出或返回参数的内容

        /// <summary>
        /// 适用返回参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public DbParameter GetParameter(DbCommand cmd, string parameterName)
        {
            return cmd.Parameters[parameterName];
        }

        #endregion

        #region 清理参数

        /// <summary>
        /// 清除参数
        /// </summary>
        /// <param name="dbCommand"></param>
        public void SetCommandParaClear(DbCommand dbCommand)
        {
            if (dbCommand != null)
            {
                dbCommand.Parameters.Clear();
            }
        }

        #endregion

        #endregion

        #region 执行

        #region 执行 ExecuteNonQuery
        /// <summary>
        /// 执行Command，并返回受影响的行数
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbCommand cmd)
        {
            int reVal = 0;
            try
            {
                cmd.Connection.Open();
                reVal = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrorLog("ExecuteNonQuery执行错误:" + cmd.CommandText, ex);
                throw ex;
            }
            finally
            {
                SetConnClose(cmd.Connection);
            }
            return reVal;
        }


        /// <summary>
        /// 执行命令
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbCommand cmd, Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            cmd.Connection.Open();
            return cmd.ExecuteNonQuery();
        }


        /// <summary>
        /// 直接执行SQL，并返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
            int reVal = 0;
            DbCommand cmd = GetSqlStringCommond(sql);
            reVal = ExecuteNonQuery(cmd);
            return reVal;
        }


        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, params DbParameter[] paras)
        {
            return ExecuteNonQuery(commandText, false, paras);
        }


        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param> 
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, bool isProcedure, params DbParameter[] paras)
        {
            int reVal = 0;
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            AddParameterCollection(ref cmd, paras);
            reVal = ExecuteNonQuery(cmd);
            return reVal;
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句或存储过程并返回受影响的行数
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(Trans trans, string commandText, bool isProcedure, params DbParameter[] paras)
        {
            int reVal = 0;
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);

            cmd.Connection = trans.DbConnection;
            cmd.Transaction = trans.DbTrans;
            AddParameterCollection(ref cmd, paras);
            cmd.Connection.Open();
            reVal = cmd.ExecuteNonQuery();
            return reVal;
        }


        /// <summary>
        /// 对连接执行Transact-SQL语句或存储过程并返回受影响的行数
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="para">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, bool isProcedure, Dictionary<string, object> para)
        {
            int reVal = 0;
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            AddInParameter(ref cmd, para);
            reVal = ExecuteNonQuery(cmd);
            return reVal;
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="para">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(Trans trans, string commandText, bool isProcedure, Dictionary<string, object> para)
        {
            int reVal = 0;
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            cmd.Connection = trans.DbConnection;
            cmd.Transaction = trans.DbTrans;
            AddInParameter(ref cmd, para);
            cmd.Connection.Open();
            reVal = cmd.ExecuteNonQuery();
            return reVal;
        }


        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="para">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(Trans trans, string commandText, Dictionary<string, object> para)
        {
            return ExecuteNonQuery(trans, commandText, false, para);
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary> 
        /// <param name="commandText">SQL语句</param>
        /// <param name="para">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, Dictionary<string, object> para)
        {
            return ExecuteNonQuery(commandText, false, para);
        }

        #endregion


        #region  执行ExecuteScalar

        /// <summary>
        /// 执行DbCommand查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public object ExecuteQueryScalar(DbCommand cmd)
        {
            object reVal = null;
            try
            {
                cmd.Connection.Open();
                reVal = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                SetConnClose(cmd.Connection);
                LogHelper.WriteErrorLog("ExecuteQueryScalar执行错误,SQL:" + cmd.CommandText, ex);
                throw ex;
            }
            finally
            {
                SetConnClose(cmd.Connection);
            }
            return reVal;
        }


        public object ExecuteScalar(DbCommand cmd, Trans trans)
        {
            cmd.Connection = trans.DbConnection;
            cmd.Transaction = trans.DbTrans;
            cmd.Connection.Open();
            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// 执行sql查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object ExecuteQueryScalar(string sql)
        {
            object reVal = null;
            DbCommand cmd = GetSqlStringCommond(sql);
            reVal = ExecuteQueryScalar(cmd);
            return reVal;
        }


        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        private object ExecuteQueryScalar(string commandText, bool isProcedure, params DbParameter[] paras)
        {
            object reVal = null;
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            AddParameterCollection(ref cmd, paras);
            reVal = ExecuteQueryScalar(cmd);
            return reVal;
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public object ExecuteQueryScalar(string commandText, params DbParameter[] paras)
        {
            return ExecuteQueryScalar(commandText, false, paras);
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public object ExecuteQueryScalar(Trans trans, string commandText, bool isProcedure, params DbParameter[] paras)
        {
            object reVal = null;
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            AddParameterCollection(ref cmd, paras);
            cmd.Connection = trans.DbConnection;
            cmd.Transaction = trans.DbTrans;
            cmd.Connection.Open();
            reVal = cmd.ExecuteScalar();
            return reVal;
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public object ExecuteQueryScalar(Trans trans, string commandText, params DbParameter[] paras)
        {
            return ExecuteQueryScalar(trans, commandText, false, paras);
        }


        /// <summary>
        /// 执行SQL查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="trans">事务处理</param>
        /// <param name="commandText">SQL</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public object ExecuteQueryScalar(Trans trans, string commandText, Dictionary<String, Object> paras)
        {
            return ExecuteQueryScalar(trans, commandText, false, paras);
        }


        /// <summary>
        /// 执行SQL查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="trans">事务处理</param>
        /// <param name="commandText">SQL</param>
        /// <param name="isProcedure">true存储过程,fals非存储过程</param>
        /// <param name="paras">参数</param>
        /// <returns></returns> 
        public object ExecuteQueryScalar(Trans trans, string commandText, bool isProcedure, Dictionary<String, Object> paras)
        {
            object reVal = null;
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            AddInParameter(ref cmd, paras);
            cmd.Connection = trans.DbConnection;
            cmd.Transaction = trans.DbTrans;
            cmd.Connection.Open();
            reVal = cmd.ExecuteScalar();
            return reVal;
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public object ExecuteQueryScalar(string commandText, bool isProcedure, Dictionary<String, Object> paras)
        {
            object reVal = null;
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            AddInParameter(ref cmd, paras);
            reVal = ExecuteQueryScalar(cmd);
            return reVal;
        }

        /// <summary>
        /// 执行查询，并返回查询结果集中的第一行第一列，忽略其它行或列
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public object ExecuteQueryScalar(string commandText, Dictionary<String, Object> paras)
        {
            return ExecuteQueryScalar(commandText, false, paras);
        }

        #endregion

        #region 执行ExecuteDataReader

        /// <summary>
        /// 执行DbCommand 带事务,返回Reader,完成后要关闭Reader
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public DbDataReader ExecuteDataReader(DbCommand cmd)
        {
            try
            {
                cmd.Connection.Open();
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (Exception ex)
            {
                SetConnClose(cmd.Connection);
                LogHelper.WriteErrorLog("ExecuteDataReader执行错误:" + cmd.CommandText, ex);
                throw ex;
            }
        }


        /// <summary>
        /// 查询返回DataReader记录集对象 如果带参数，不建议直接用SQL
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public DbDataReader ExecuteDataReader(string sql)
        {
            DbCommand cmd = GetSqlStringCommond(sql);
            return ExecuteDataReader(cmd);
        }



        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public DbDataReader ExecuteDataReader(string commandText, bool isProcedure, params DbParameter[] paras)
        {
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            AddParameterCollection(ref cmd, paras);
            return this.ExecuteDataReader(cmd);
        }

        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public DbDataReader ExecuteDataReader(string sql, params DbParameter[] paras)
        {
            return ExecuteDataReader(sql, false, paras);
        }

        /// <summary>
        /// 执行SQL,返回DataReader,必须手动关闭DataReader
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DbDataReader ExecuteDataReader(string commandText, string sql, params DbParameter[] paras)
        {
            return ExecuteDataReader(sql, false, paras);
        }

        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public DbDataReader ExecuteDataReader(string commandText, bool isProcedure, Dictionary<string, object> paras)
        {
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            SetConnClose(cmd.Connection);
            AddInParameter(ref cmd, paras);
            try
            {
                cmd.Connection.Open();
                DbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrorLog("ExecuteDataReader执行错误:" + commandText, ex);
                SetConnClose(cmd.Connection);
                throw ex;
            }
        }

        /// <summary>
        /// 执行SQL，并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="sql">SQL语句</param> 
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public DbDataReader ExecuteDataReader(string sql, Dictionary<string, object> paras)
        {
            DbCommand cmd = GetSqlStringCommond(sql);
            SetConnClose(cmd.Connection);
            AddInParameter(ref cmd, paras);
            return ExecuteDataReader(sql, false, paras);
        }

        #endregion

        #region 执行ExecuteDataSet
        /// <summary>
        /// 执行DbCommand ,返回DataSet
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(DbCommand cmd)
        {
            var ds = new DataSet();
            try
            {
                DbProviderFactory dbfactory = DbProviderFactories.GetFactory(dbProviderName);
                DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
                if (dbDataAdapter != null)
                {
                    dbDataAdapter.SelectCommand = cmd;
                    cmd.Connection.Open();
                    dbDataAdapter.Fill(ds);
                }
            }
            catch (Exception ex)
            {
                SetConnClose(cmd.Connection);
                LogHelper.WriteErrorLog("ExecuteDataSet执行错误,SQL:" + cmd.CommandText, ex);
                throw ex;
            }
            finally
            {
                SetConnClose(cmd.Connection);
            }
            return ds;
        }


        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string commandText, bool isProcedure, params DbParameter[] paras)
        {
            DataSet reVal = null;
            DbCommand cmd = isProcedure ? GetStoredProcCommond(commandText) : GetSqlStringCommond(commandText);
            AddParameterCollection(ref cmd, paras);
            reVal = ExecuteDataSet(cmd);
            return reVal;
        }

        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="paras">DbParameter参数列表，0个或多个参数</param>
        /// <returns></returns>

        public DataSet ExecuteDataSet(string sql, params DbParameter[] paras)
        {
            return ExecuteDataSet(sql, false, paras);
        }

        /// <summary>
        /// 执行SQL或存储过程，并返回DataSet结果集
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="isProcedure">true存储过程,false SQL</param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string commandText, bool isProcedure, Dictionary<string, object> paras)
        {
            DataSet reVal = new DataSet();
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            AddInParameter(ref cmd, paras);
            return ExecuteDataSet(cmd);
        }


        /// <summary>
        /// 执行SQL，并返回DataSet结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql, Dictionary<string, object> paras)
        {
            DataSet reVal = null;
            DbCommand cmd = GetSqlStringCommond(sql);
            AddInParameter(ref cmd, paras);
            return ExecuteDataSet(cmd);
        }

        #endregion


        #region 执行DataTable
        /// <summary>
        /// 执行DbCommand ,返回DataSet
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(DbCommand cmd)
        {
            var dt = new DataTable();
            try
            {
                DbProviderFactory dbfactory = DbProviderFactories.GetFactory(dbProviderName);
                DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
                if (dbDataAdapter != null)
                {
                    dbDataAdapter.SelectCommand = cmd;
                    cmd.Connection.Open();
                    dbDataAdapter.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                SetConnClose(cmd.Connection);
                LogHelper.WriteErrorLog("ExecuteDataTable执行错误,SQL:" + cmd.CommandText, ex);
                throw ex;
            }
            finally
            {
                SetConnClose(cmd.Connection);
            }
            return dt;
        }

        /// <summary>
        /// 执行SQL，带参数的查询返回表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, params DbParameter[] paras)
        {
            DbCommand cmd = GetSqlStringCommond(sql);
            AddParameterCollection(ref cmd, paras);
            return ExecuteDataTable(cmd);
        }


        /// <summary>
        /// 执行SQL，带参数的查询返回表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql)
        {
            DataTable dt = null;
            DbCommand cmd = GetSqlStringCommond(sql);
            dt = ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 执行SQL或者存储过程，带参数的查询返回表
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="isProcedure"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText, bool isProcedure, params DbParameter[] paras)
        {
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            AddParameterCollection(ref cmd, paras);
            return ExecuteDataTable(cmd);
        }



        /// <summary>
        /// 执行SQL或者存储过程，带参数的查询返回表
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="isProcedure"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText, bool isProcedure, Dictionary<string, object> paras)
        {
            DbCommand cmd = isProcedure ? GetSqlStringCommond(commandText) : GetStoredProcCommond(commandText);
            AddInParameter(ref cmd, paras);
            return ExecuteDataTable(cmd);
        }

        /// <summary>
        /// 执行SQL，带参数的查询返回表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, Dictionary<string, object> paras)
        {
            DbCommand cmd = GetSqlStringCommond(sql);
            AddInParameter(ref cmd, paras);
            return ExecuteDataTable(cmd);
        }



        #endregion

        #endregion

        #region DataReader 转dataTable

        //         <summary>
        //         DataReader 转dataTable
        //         </summary>
        //         <param name="rs"></param>
        //         <returns></returns>
        public DataTable ChangeReaderToDataTable(IDataReader rs)
        {
            DataTable dataTable = new DataTable();
            try
            {
                using (rs)
                {
                    dataTable.Load(rs);
                }

                rs.Close();
                rs.Dispose();
            }
            catch (Exception ex)
            {
                rs.Close();
                rs.Dispose();
                LogHelper.WriteErrorLog("DBHelper >> public DataTable ChangeReaderToDataTable(IDataReader rs)", ex);
                throw;
            }
            finally
            {
                rs.Close();
                rs.Dispose();
            }
            return dataTable;
        }

        #endregion

        #region 批量插入数据

        /// <summary>
        /// 批量插入数据 DataTable
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="BatchSize">每次提交 的条数</param>
        /// <returns></returns>
        public int SqlBulkCopyInsert(DataTable dataTable, int BatchSize)
        {
            return SqlBulkCopyInsert(dataTable, BatchSize, dataTable.TableName);
        }

        /// <summary>
        /// 批量插入数据 DataTable
        /// </summary>
        /// <param name="dataTable">要提交的数据</param>
        /// <param name="BatchSize">每次提交 的条数</param>
        /// <param name="TableName">服务器上目标表的名称</param>
        /// <returns></returns>
        public int SqlBulkCopyInsert(DataTable dataTable, int BatchSize, string TableName)
        {
            int reVal = dataTable.Rows.Count;
            using (SqlConnection sqlConn = new SqlConnection(dbConnectionString))
            {
                sqlConn.Open();
                SqlTransaction tran = sqlConn.BeginTransaction();
                using (SqlBulkCopy bulkCoye = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, tran))
                {
                    bulkCoye.DestinationTableName = TableName;
                    bulkCoye.BatchSize = BatchSize;
                    try
                    {
                        if (dataTable != null && dataTable.Rows.Count != 0)
                        {
                            bulkCoye.WriteToServer(dataTable);
                            // throw (new Exception("测试错误！"));
                            tran.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        reVal = 0;
                        tran.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        bulkCoye.Close();
                        sqlConn.Close();
                    }
                }
            }
            return reVal;
        }

        #endregion
    }


    /// <summary>
    /// 事务
    /// </summary>
    public class Trans : IDisposable
    {
        private readonly DbConnection conn;
        private readonly DbTransaction dbTrans;

        public DbConnection DbConnection
        {
            get { return this.conn; }
        }

        public DbTransaction DbTrans
        {
            get { return this.dbTrans; }
        }

        public Trans()
        {
            conn = DBHelper.CreateConnection();
            conn.Open();
            dbTrans = conn.BeginTransaction();
        }

        public Trans(string connectionString)
        {
            conn = DBHelper.CreateConnection(connectionString);
            conn.Open();
            dbTrans = conn.BeginTransaction();
        }

        public void Commit()
        {
            dbTrans.Commit();
            this.Colse();
        }

        public void RollBack()
        {
            dbTrans.Rollback();
            this.Colse();
        }

        public void Dispose()
        {
            this.Colse();
        }

        public void Colse()
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }
}
